.pragma library

var SqlCreateTable = [];
SqlCreateTable["setting"] = "CREATE TABLE IF NOT EXISTS Settings(key TEXT, value TEXT)";
SqlCreateTable["keyword"] = "CREATE TABLE IF NOT EXISTS UserKeyword (user_id TEXT, category TEXT, keyword TEXT, update_at TEXT)";
SqlCreateTable["history"] = "CREATE TABLE IF NOT EXISTS UserHistory (user_id TEXT, asin TEXT, update_at TEXT)";
SqlCreateTable["userlist"] = "CREATE TABLE IF NOT EXISTS UserList(user_id TEXT, value TEXT, item_count TEXT)";
SqlCreateTable["commandhistory"] = "CREATE TABLE IF NOT EXISTS CommandHistory (user_id TEXT, status_id TEXT, update_at TEXT)";

var SqlDropTable = [];
SqlDropTable["setting"] = "DROP TABLE IF EXISTS Settings";
SqlDropTable["keyword"] = "DROP TABLE IF EXISTS UserKeyword";
SqlDropTable["history"] = "DROP TABLE IF EXISTS UserHistory";
SqlDropTable["userlist"] = "DROP TABLE IF EXISTS UserList";
SqlDropTable["commandhistory"] = "DROP TABLE IF EXISTS CommandHistory";


var _maxKeywordCount = 10;

// 更新情報の文字列取得
function getUpdateAtString(){
    return Qt.formatDateTime(new Date(), "yyyy/MM/dd hh:mm:ss");
}

// 設定テーブルを削除
function dropSetting(){
    db().transaction(
        function(tx) {
            dropTable(tx, "setting");
        }
    )
}

// 設定データを読む
function readData(key) {
    var ret = undefined;
    db().transaction(
        function(tx) {
            createTable(tx, "setting");

            var rs = tx.executeSql('SELECT value FROM Settings WHERE key=?', key);

            if (rs.rows.length > 0) {
                ret = rs.rows.item(0).value;
            }
        }
    )
    return ret;
}

// 設定データを保存
function saveData(key, value) {
    db().transaction(
        function(tx) {
            createTable(tx, "setting");

            var currentValue = readData(key);
            if (currentValue != value) {
                if (currentValue !== undefined) {
                    tx.executeSql('UPDATE Settings SET value = ? WHERE key = ?', [ value, key ]);
                } else {
                    tx.executeSql('INSERT INTO Settings VALUES(?, ?)', [ key, value ]);
                }
            }
        }
    )
}



// キーワードのテーブルの削除
function dropKeyword(){
    db().transaction(
        function(tx) {
            dropTable(tx, "keyword");
        }
    )
}

// キーワードのクリア
function clearKeyword(user_id){
    db().transaction(
        function(tx) {
            createTable(tx, "keyword");

            var rs = tx.executeSql("DELETE FROM UserKeyword WHERE user_id=?"
                                   , user_id);
        }
    )
}

// キーワードを取得
function readKeyword(user_id){
    var ret = [];
    db().transaction(
        function(tx) {
            createTable(tx, "keyword");
            var rs = null;
            if(user_id.length > 0){
                rs = tx.executeSql("SELECT user_id, category, keyword, update_at FROM UserKeyword WHERE user_id=? ORDER BY update_at"
                                   , user_id);
            }else{
                rs = tx.executeSql("SELECT user_id, category, keyword, update_at FROM UserKeyword ORDER BY update_at");
            }
            for(var i=0; i<rs.rows.length; i++){
                rs.rows.item(i).page_num = 1;
                ret.push(rs.rows.item(i));
            }
        }
    )
    return ret;
}
// キーワードを保存
function saveKeyword(user_id, category, keyword, item_count) {
    var ret = 0;
    db().transaction(
        function(tx) {
            createTable(tx, "keyword");

            var rs = tx.executeSql("SELECT user_id, category, keyword, update_at FROM UserKeyword "
                                   + " WHERE user_id=? AND category=? AND keyword=?"
                                   , [user_id, category, keyword]);
            var update_at = getUpdateAtString();
            if (rs.rows.length > 0) {
                // 既にある
                tx.executeSql("UPDATE UserKeyword SET update_at = ? "
                              + "WHERE user_id=? AND category=? AND keyword=?"
                              , [update_at, user_id, category, keyword]);

                console.debug("exist keyword:" + rs.rows.item(0).user_id
                              + "," + rs.rows.item(0).category
                              + "," + rs.rows.item(0).keyword
                              + "," + rs.rows.item(0).update_at
                              );
            }else{
                // 埋まりきってたら古いのを切り捨てる
                rs = tx.executeSql("SELECT user_id, category, keyword, update_at FROM UserKeyword "
                                       + " WHERE user_id=? ORDER BY update_at"
                                       , user_id);
                console.debug("len=" + rs.rows.length + ", count=" + item_count);
                var over_count = (rs.rows.length - item_count);
                while(over_count >= 0){
                    tx.executeSql("DELETE FROM UserKeyword WHERE user_id=? AND category=? AND keyword=?"
                                  , [user_id
                                  , rs.rows.item(over_count).category
                                  , rs.rows.item(over_count).keyword]);
                    console.debug("save keyword:" + user_id
                                  + "," + rs.rows.item(over_count).category
                                  + "," + rs.rows.item(over_count).keyword
                                  + "," + rs.rows.item(over_count).update_at
                                  );
                    over_count--;
                }

                // 追加
                tx.executeSql('INSERT INTO UserKeyword VALUES(?, ?, ?, ?)'
                              , [ user_id, category, keyword, update_at ]);

                console.debug("save keyword:" + user_id
                              + "," + category
                              + "," + keyword
                              + "," + update_at
                              );

                ret = 1;
            }
        }
    )
    return ret;
}


// 履歴テーブルの削除
function dropHistory(){
    db().transaction(
        function(tx) {
            dropTable(tx, "history");
        }
    )
}

// 履歴のクリア
function clearHistory(user_id){
    db().transaction(
        function(tx) {
            createTable(tx, "history");

            var rs = tx.executeSql("DELETE FROM UserHistory WHERE user_id=?"
                                   , user_id);
        }
    )
}

// 履歴の配列取得
function readHistory(user_id){
    var ret = [];
    db().transaction(
        function(tx) {
            createTable(tx, "history");

            var rs = tx.executeSql("SELECT user_id, asin, update_at FROM UserHistory WHERE user_id=? ORDER BY update_at"
                                   , user_id);
            for(var i=0; i<rs.rows.length; i++){
                console.debug("read:" + rs.rows.item(i).asin);
                ret.push(rs.rows.item(i));
            }
        }
    )
    return ret;
}

// 履歴を保存
function saveHistory(user_id, asin) {
    var ret = false;
    db().transaction(
        function(tx) {
            createTable(tx, "history");

            var rs = tx.executeSql("SELECT user_id, asin, update_at FROM UserHistory WHERE user_id=? AND asin=? ORDER BY update_at"
                                   , [user_id, asin]);
            if (rs.rows.length > 0) {
                // 既にある
                console.debug("exist history:" + rs.rows.item(0).user_id
                              + "," + rs.rows.item(0).asin
                              + "," + rs.rows.item(0).update_at
                              );
            }else{
                var update_at = getUpdateAtString();
                tx.executeSql('INSERT INTO UserHistory VALUES(?, ?, ?)', [ user_id, asin, update_at ]);

                ret = true;
                console.debug("save history:" + user_id + "," + asin);
            }
        }
    )
    return ret;
}



// ユーザーリストを削除
function dropUserList(){
    db().transaction(
        function(tx) {
            dropTable(tx, "userlist");
        }
    )
}

// ユーザーを削除する
function removeUser(user_id){
    db().transaction(
        function(tx) {
            createTable(tx, "userlist");

            var rs = tx.executeSql("DELETE FROM UserList WHERE user_id=?"
                                   , user_id);
        }
    )
}

// ユーザーリストを読む
function readUserList() {
    var ret = [];
    db().transaction(
        function(tx) {
            createTable(tx, "userlist");

            var rs = tx.executeSql('SELECT user_id, item_count FROM UserList');

            for(var i=0; i<rs.rows.length; i++){
                console.debug("read:" + rs.rows.item(i).user_id);
                ret.push(rs.rows.item(i));
            }
        }
    )
    return ret;
}

// ユーザー情報を読む
function readUserInfo(user_id){
    var ret = [];
    db().transaction(
        function(tx) {
            createTable(tx, "userlist");

            var rs = tx.executeSql('SELECT user_id, item_count FROM UserList WHERE user_id = ?'
                                   , user_id);
            if(rs.rows.length > 0){
                ret.push(rs.rows.item(0));
                console.debug("read user(single):" + rs.rows.item(0).user_id + "," + rs.rows.item(0).item_count);
            }
        }
    )
    return ret;
}

// ユーザー情報をアップデート
function updateUserInfo(user_id, item_count){
    var ret = 0;
    db().transaction(
        function(tx) {
            createTable(tx, "userlist");

            var rs = tx.executeSql('SELECT user_id FROM UserList WHERE user_id = ?'
                                , user_id);
            if (rs.rows.length != 1) {
                // そんなユーザーいない
                console.debug("unknown user:" + user_id);
            } else {
                // 既にある
                console.debug("exist user:" + rs.rows.item(0).user_id);

                tx.executeSql("UPDATE UserList SET item_count = ?"
                              + "WHERE user_id=?"
                              , [item_count, user_id]);
                ret = 1;
            }
        }
    )
    return ret;
}

// ユーザーリストを保存
function saveUserList(user_id, item_count) {//, value) {
    var ret = 0;
    db().transaction(
        function(tx) {
            createTable(tx, "userlist");

            var value = "";

            var rs = tx.executeSql("SELECT user_id FROM UserList WHERE user_id=?", user_id);
            if (rs.rows.length > 0) {
                // 既にある
                console.debug("exist user:" + rs.rows.item(0).user_id);
            } else {
                tx.executeSql('INSERT INTO UserList VALUES(?, ?, ?)', [ user_id, value, item_count ]);
                console.debug("save user:" + user_id);

                ret = 1;
            }
        }
    )
    return ret;
}



// コマンド履歴テーブルを削除
function dropCommandHistory(){
    db().transaction(
        function(tx) {
            dropTable(tx, "commandhistory");
        }
    )
}

// コマンド履歴のクリア
function clearCommandHistory(user_id){
    db().transaction(
        function(tx) {
            createTable(tx, "commandhistory");

            var rs = tx.executeSql("DELETE FROM CommandHistory WHERE user_id=?"
                                   , user_id);
        }
    )
}

// コマンド履歴を取得
function readCommandHistory(user_id){
    var ret = [];
    db().transaction(
        function(tx) {
            createTable(tx, "commandhistory");

            var rs = tx.executeSql('SELECT user_id, status_id FROM CommandHistory WHERE user_id = ?'
                                   , user_id);
            for(var i=0; i<rs.rows.length; i++){
                console.debug("read command history:" + rs.rows.item(i).user_id + ", " + rs.rows.item(i).status_id);
                ret.push(rs.rows.item(i));
            }
        }
    )
    return ret;
}

// コマンド履歴を保存
function saveCommandHistory(user_id, status_id) {
    var ret = false;
    db().transaction(
        function(tx) {
            createTable(tx, "commandhistory");

            var rs = tx.executeSql("SELECT status_id FROM CommandHistory WHERE status_id=?"
                                   , status_id);
            if (rs.rows.length > 0) {
                // 既にある
                console.debug("exist command history:" + rs.rows.item(0).status_id
                              );
            }else{
                var update_at = getUpdateAtString();
                tx.executeSql('INSERT INTO CommandHistory VALUES(?, ?, ?)', [ user_id, status_id, update_at ]);

                ret = true;
                console.debug("save command history:" + user_id + ", " + status_id);
            }
        }
    )
    return ret;
}



function db() {
    return openDatabaseSync("SAKIDORITTER", "1.0", "Data storage for Sakidoritter", 10000000);
}

function createTable(tx, table) {
    tx.executeSql(SqlCreateTable[table]);
}
function dropTable(tx, table) {
    tx.executeSql(SqlDropTable[table]);
}
